package com.plugin.manage.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.plugin.core.model.entity.ElderConsultation;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.time.LocalDateTime;
import java.util.List;
import java.util.Map;

/**
 * 咨询记录Mapper接口
 *
 * @author mark
 * @date 2025-07-30
 */
@Mapper
public interface ConsultationMapper extends BaseMapper<ElderConsultation> {

    /**
     * 统计咨询记录总数
     * 
     * @param startTime 开始时间
     * @param endTime 结束时间
     * @return 咨询记录总数
     */
    @Select("SELECT COUNT(*) FROM elder_consultation WHERE del = 0 " +
            "AND create_time >= #{startTime} AND create_time <= #{endTime}")
    Long countTotalConsultations(@Param("startTime") LocalDateTime startTime, 
                                 @Param("endTime") LocalDateTime endTime);

    /**
     * 统计正在跟进中的咨询记录数量
     * 
     * @param startTime 开始时间
     * @param endTime 结束时间
     * @return 正在跟进中的咨询记录数量
     */
    @Select("SELECT COUNT(*) FROM elder_consultation WHERE del = 0 AND status = 1 " +
            "AND create_time >= #{startTime} AND create_time <= #{endTime}")
    Long countActiveConsultations(@Param("startTime") LocalDateTime startTime, 
                                  @Param("endTime") LocalDateTime endTime);

    /**
     * 统计已签约的咨询记录数量
     * 
     * @param startTime 开始时间
     * @param endTime 结束时间
     * @return 已签约的咨询记录数量
     */
    @Select("SELECT COUNT(*) FROM elder_consultation WHERE del = 0 AND status = 2 " +
            "AND create_time >= #{startTime} AND create_time <= #{endTime}")
    Long countSignedConsultations(@Param("startTime") LocalDateTime startTime, 
                                  @Param("endTime") LocalDateTime endTime);

    /**
     * 获取销售人员咨询统计TOP5
     * 
     * @param startTime 开始时间
     * @param endTime 结束时间
     * @return 销售人员咨询统计TOP5
     */
    @Select("SELECT " +
            "    es.sale_name, " +
            "    es.sale_code, " +
            "    COUNT(ec.id) as total_count, " +
            "    SUM(CASE WHEN ec.status = 1 THEN 1 ELSE 0 END) as active_count, " +
            "    SUM(CASE WHEN ec.status = 2 THEN 1 ELSE 0 END) as signed_count, " +
            "    CASE " +
            "        WHEN COUNT(ec.id) = 0 THEN 0.0 " +
            "        ELSE ROUND((SUM(CASE WHEN ec.status = 2 THEN 1 ELSE 0 END) / COUNT(ec.id)) * 100, 2) " +
            "    END as conversion_rate " +
            "FROM elder_sales es " +
            "LEFT JOIN elder_consultation ec ON CONVERT(es.sale_code USING utf8mb4) COLLATE utf8mb4_general_ci = CONVERT(ec.sale_code USING utf8mb4) COLLATE utf8mb4_general_ci " +
            "    AND ec.del = 0 " +
            "    AND ec.create_time >= #{startTime} " +
            "    AND ec.create_time <= #{endTime} " +
            "WHERE es.del = 0 AND es.status = 1 " +
            "GROUP BY es.sale_code, es.sale_name " +
            "HAVING COUNT(ec.id) > 0 " +
            "ORDER BY conversion_rate DESC, signed_count DESC " +
            "LIMIT 5")
    List<Map<String, Object>> getSalesPerformanceTop5(@Param("startTime") LocalDateTime startTime, 
                                                      @Param("endTime") LocalDateTime endTime);

    /**
     * 获取媒介渠道统计信息
     * 
     * @param startTime 开始时间
     * @param endTime 结束时间
     * @return 媒介渠道统计信息
     */
    @Select("SELECT " +
            "    channel_type, " +
            "    COUNT(*) as reception_count, " +
            "    SUM(CASE WHEN status = 2 THEN 1 ELSE 0 END) as signed_count, " +
            "    CASE " +
            "        WHEN COUNT(*) = 0 THEN 0.0 " +
            "        ELSE ROUND((SUM(CASE WHEN status = 2 THEN 1 ELSE 0 END) / COUNT(*)) * 100, 2) " +
            "    END as conversion_rate " +
            "FROM elder_consultation " +
            "WHERE del = 0 " +
            "    AND channel_type IN (1, 2, 3, 4, 5) " +
            "    AND create_time >= #{startTime} " +
            "    AND create_time <= #{endTime} " +
            "GROUP BY channel_type " +
            "ORDER BY channel_type")
    List<Map<String, Object>> getChannelStatistics(@Param("startTime") LocalDateTime startTime, 
                                                   @Param("endTime") LocalDateTime endTime);

    /**
     * 获取咨询仪表盘列表 - 三表联查
     * 联查 elder_consultation、elder_info、elder_sales 三张表
     * 支持按咨询人、状态、销售姓名、咨询时间区间筛选
     */
    @Select("SELECT " +
            "    ec.consultant_name as consultant_name, " +
            "    ec.relation as relation, " +
            "    ei.realname as elder_name, " +
            "    ei.age as age, " +
            "    ec.purpose as purpose, " +
            "    ec.consultant_time as consultant_time, " +
            "    es.sale_name as sale_name, " +
            "    ec.status as status " +
            "FROM elder_consultation ec " +
            "LEFT JOIN elder_info ei ON ec.elder_id = ei.elder_id " +
            "LEFT JOIN elder_sales es ON CONVERT(ec.sale_code USING utf8mb4) COLLATE utf8mb4_general_ci = CONVERT(es.sale_code USING utf8mb4) COLLATE utf8mb4_general_ci " +
            "WHERE ec.del = 0 " +
            "    AND (#{consultantName} IS NULL OR ec.consultant_name LIKE CONCAT('%', #{consultantName}, '%')) " +
            "    AND (#{status} IS NULL OR ec.status = #{status}) " +
            "    AND (#{saleName} IS NULL OR es.sale_name LIKE CONCAT('%', #{saleName}, '%')) " +
            "    AND (#{startTime} IS NULL OR ec.consultant_time >= #{startTime}) " +
            "    AND (#{endTime} IS NULL OR ec.consultant_time <= #{endTime}) " +
            "ORDER BY ec.consultant_time DESC")
    List<Map<String, Object>> getDashboardList(@Param("consultantName") String consultantName,
                                               @Param("status") Integer status,
                                               @Param("saleName") String saleName,
                                               @Param("startTime") LocalDateTime startTime,
                                               @Param("endTime") LocalDateTime endTime);

    /**
     * 获取咨询仪表盘列表（分页） - 三表联查
     * 使用MyBatis-Plus分页插件，自动处理分页逻辑
     * 联查 elder_consultation、elder_info、elder_sales 三张表
     * 支持按咨询人、状态、销售姓名、咨询时间区间筛选
     */
    @Select("SELECT " +
            "    ec.consultant_name as consultant_name, " +
            "    ec.relation as relation, " +
            "    ei.realname as elder_name, " +
            "    ei.age as age, " +
            "    ec.purpose as purpose, " +
            "    ec.consultant_time as consultant_time, " +
            "    es.sale_name as sale_name, " +
            "    ec.status as status " +
            "FROM elder_consultation ec " +
            "LEFT JOIN elder_info ei ON ec.elder_id = ei.elder_id " +
            "LEFT JOIN elder_sales es ON CONVERT(ec.sale_code USING utf8mb4) COLLATE utf8mb4_general_ci = CONVERT(es.sale_code USING utf8mb4) COLLATE utf8mb4_general_ci " +
            "WHERE ec.del = 0 " +
            "    AND (#{consultantName} IS NULL OR ec.consultant_name LIKE CONCAT('%', #{consultantName}, '%')) " +
            "    AND (#{status} IS NULL OR ec.status = #{status}) " +
            "    AND (#{saleName} IS NULL OR es.sale_name LIKE CONCAT('%', #{saleName}, '%')) " +
            "    AND (#{startTime} IS NULL OR ec.consultant_time >= #{startTime}) " +
            "    AND (#{endTime} IS NULL OR ec.consultant_time <= #{endTime}) " +
            "ORDER BY ec.consultant_time DESC")
    IPage<Map<String, Object>> getDashboardListPaged(IPage<Map<String, Object>> page,
                                                     @Param("consultantName") String consultantName,
                                                     @Param("status") Integer status,
                                                     @Param("saleName") String saleName,
                                                     @Param("startTime") LocalDateTime startTime,
                                                     @Param("endTime") LocalDateTime endTime);
}
